use crate::error::AppErr;
use crate::error::SqlxErr;
use crate::error::UniqueMap;
use serde::Serialize;
use sqlx::Executor;
use sqlx::Row;
use sqlx::SqliteConnection;

use super::device;
use super::firm_file;
use super::get_pool;

// pub const TY_BIN: &'static str = "bin";
// pub const TY_HEX: &'static str = "hex";
// pub const TY_BOOTLOADER_HEX: &'static str = "bootloader_hex";
// pub const TY_APK: &'static str = "apk";
// pub const TY_EXE: &'static str = "exe";
// pub const TY_UNKNOWN: &'static str = "unknown";

pub const CREATE_SQL: &'static str = r#"
    CREATE TABLE IF NOT EXISTS tb_firm_type (
        id INTEGER PRIMARY KEY AUTOINCREMENT, 
        proj_id INTEGER NOT NULL, 
        name TEXT NOT NULL, 
        description TEXT NOT NULL, 
        version INTEGER NOT NULL, 
        ty TEXT NOT NULL, 
        target TEXT NOT NULL, 
        UNIQUE (name, version, ty)
    )
"#;

pub async fn init() -> Result<(), AppErr> {
    get_pool().execute(CREATE_SQL).await?;
    Ok(())
}

#[derive(Debug, Serialize)]
pub struct TbFirmType {
    id: i64,
    name: String,
    description: String,
    version: u32,
    ty: String,
    target: String,
}

pub async fn query(proj_id: i64) -> Result<Box<[TbFirmType]>, AppErr> {
    let rows = sqlx::query(
        r#"
        SELECT id, name, description, version, ty, target FROM tb_firm_type 
        WHERE proj_id = ? ORDER BY id DESC
    "#,
    )
    .bind(proj_id)
    .fetch_all(get_pool())
    .await?;
    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let item = TbFirmType {
            id: row.get(0),
            name: row.get(1),
            description: row.get(2),
            version: row.get(3),
            ty: row.get(4),
            target: row.get(5),
        };
        vec.push(item);
    }
    Ok(vec.into_boxed_slice())
}

pub async fn create(
    proj_id: i64,
    name: &str,
    description: &str,
    version: u32,
    ty: &str,
    target: &str,
) -> Result<i64, AppErr> {
    let ret = sqlx::query(
        r#"
        INSERT INTO tb_firm_type (
            proj_id, name, description, version, ty, target 
        ) VALUES (?, ?, ?, ?, ?, ?)
    "#,
    )
    .bind(proj_id)
    .bind(name)
    .bind(description)
    .bind(version)
    .bind(ty)
    .bind(target)
    .execute(get_pool())
    .await
    .map_unique("名称/版本号/类型重复")?;

    Ok(ret.last_insert_rowid())
}

pub async fn delete(id: i64) -> Result<(), AppErr> {
    let mut tx = get_pool().begin().await?;
    sqlx::query("DELETE FROM tb_firm_type WHERE id = ?")
        .bind(id)
        .execute(&mut *tx)
        .await?;
    firm_file::delete_with_type(id, &mut tx).await?;
    device::delete_with_type(id, &mut tx).await?;
    tx.commit().await?;
    Ok(())
}

pub async fn delete_with_proj(proj_id: i64, conn: &mut SqliteConnection) -> Result<(), SqlxErr> {
    sqlx::query(
        "DELETE FROM tb_firm_file WHERE type_id IN (SELECT id FROM tb_firm_type WHERE proj_id = ?)",
    )
    .bind(proj_id)
    .execute(&mut *conn)
    .await?;

    sqlx::query(
        "DELETE FROM tb_device WHERE type_id IN (SELECT id FROM tb_firm_type WHERE proj_id = ?)"
    ).bind(proj_id)
    .execute(&mut *conn)
    .await?;

    sqlx::query(
        r#"
        DELETE FROM tb_firm_type WHERE proj_id = ?
    "#,
    )
    .bind(proj_id)
    .execute(conn)
    .await?;
    Ok(())
}
